package com.egao.orderB.biz.impl;

import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;

import com.egao.common.core.damon.utils.Utils;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.ExStorageBiz;
import com.egao.orderB.entity.ExStorage;
import com.egao.common.core.web.JsonResult;
import com.egao.orderB.entity.Stock;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2022-01-07 17:09:31
 * @version ：1.0
 */
@Repository
public class ExStorageBizImpl extends BaseDao implements ExStorageBiz {

	@Override
	public JsonResult list(PageResult2<T> page, ExStorage exStorage, User loginUser) throws Exception {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ExStorage> rm = new BeanPropertyRowMapper<ExStorage>(ExStorage.class);
		StringBuffer sql = new StringBuffer("select e.id,e.organization_id,e.put_storage_ids,e.specify_job_number");
    sql.append(",e.po_code,e.reference_id,e.so_code,e.customer_code");
		sql.append(",e.transfer_order_no,e.start_date,e.warehousing_date,e.arrival_date");
		sql.append(",e.vessel,e.voyage,e.carrier,e.product_name");
		sql.append(",e.box_type,e.bill_of_lading,e.port_of_departure,e.port_of_destination");
		sql.append(",e.product_type,e.pack_num,e.track_edi,e.delivery_state");
		sql.append(",e.total_gross_weight,e.volume_m3,e.cost_weight,e.weight_conversion_factor");
		sql.append(",e.billing_ton,e.bubble_separation,e.num,e.weight");
		sql.append(",e.volume,e.warehouse_name,e.billing_method,e.order_num");
		sql.append(",e.create_time,e.create_user,e.update_time,e.update_user");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_ex_storage e ");
    sql.append(" inner join sys_user suser on suser.user_id=e.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=e.organization_id and sogzn.deleted=0 ");
    sql.append(" where e.deleted=0 ");
		if(loginUser.getUserId() != UserConstant.ADMIN_ID){
      sql.append(" and e.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if(Utils.isNotEmpty(exStorage)){
			if(Utils.isNotEmpty(exStorage.getBillOfLading())){
				sql.append(" and instr(e.bill_of_lading,:bill_of_lading)>0 ");
				map.put("bill_of_lading", exStorage.getBillOfLading());
			}
			if(Utils.isNotEmpty(exStorage.getVessel())){
				sql.append(" and instr(e.vessel,:vessel)>0 ");
				map.put("vessel", exStorage.getVessel());
			}
			if(Utils.isNotEmpty(exStorage.getVoyage())){
				sql.append(" and instr(e.voyage,:voyage)>0 ");
				map.put("voyage", exStorage.getVoyage());
			}
			if (Utils.isNotEmpty(exStorage.getDate1())) {
				sql.append(" and e.warehousing_date>= :date1 ");
				map.put("date1", exStorage.getDate1());
			}
			if (Utils.isNotEmpty(exStorage.getDate2())) {
				sql.append(" and e.warehousing_date<=:date2 ");
				map.put("date2", exStorage.getDate2());
			}
      if(Utils.isNotEmpty(exStorage.getSort())&&Utils.isNotEmpty(exStorage.getOrder())){
        sql.append(" order by e."+exStorage.getSort()+" "+exStorage.getOrder());
      } else {
        sql.append(" order by e.create_time desc");
      }
		}
		return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public List<ExStorage> list(ExStorage exStorage, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ExStorage> rm = new BeanPropertyRowMapper<ExStorage>(ExStorage.class);
    StringBuffer sql = new StringBuffer("select e.id,e.organization_id,e.put_storage_ids,e.specify_job_number");
    sql.append(",e.po_code,e.reference_id,e.so_code,e.customer_code");
		sql.append(",e.transfer_order_no,e.start_date,e.warehousing_date,e.arrival_date");
		sql.append(",e.vessel,e.voyage,e.carrier,e.product_name");
		sql.append(",e.box_type,e.bill_of_lading,e.port_of_departure,e.port_of_destination");
		sql.append(",e.product_type,e.pack_num,e.track_edi,e.delivery_state");
		sql.append(",e.total_gross_weight,e.volume_m3,e.cost_weight,e.weight_conversion_factor");
		sql.append(",e.billing_ton,e.bubble_separation,e.num,e.weight");
		sql.append(",e.volume,e.warehouse_name,e.billing_method,e.order_num");
		sql.append(",e.create_time,e.create_user,e.update_time,e.update_user");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_ex_storage e ");
    sql.append(" inner join sys_user suser on suser.user_id=e.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=e.organization_id and sogzn.deleted=0 ");
    sql.append(" where e.deleted=0 ");
		if(loginUser.getUserId() != UserConstant.ADMIN_ID){
      sql.append(" and e.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if(Utils.isNotEmpty(exStorage)){
      if(Utils.isNotEmpty(exStorage.getSort())&&Utils.isNotEmpty(exStorage.getOrder())){
        sql.append(" order by e."+exStorage.getSort()+" "+exStorage.getOrder());
      } else {
        sql.append(" order by e.create_time desc");
      }
    }
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<ExStorage> listVolume(ExStorage exStorage, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ExStorage> rm = new BeanPropertyRowMapper<ExStorage>(ExStorage.class);
		StringBuffer sql = new StringBuffer("");
//		if (exStorage.getPoCode().equals("day")) {
//			sql.append(" select sum(eg.total_volume) from chf_ex_storage es ");
//			sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id ");
//			sql.append(" where ps.warehousing_date=CURDATE() ");
//		} else if (exStorage.getPoCode().equals("week")) {
//			sql.append(" select sum(eg.total_volume) from chf_ex_storage es ");
//			sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id ");
//			sql.append(" where YEARWEEK(DATE_FORMAT(ps.warehousing_date,'%Y-%m-%d')) = YEARWEEK(NOW()) ");
//		} else if (exStorage.getPoCode().equals("quarter")) {
//			sql.append(" select sum(eg.total_volume) from chf_ex_storage es ");
//			sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id ");
//			sql.append(" where QUARTER(ps.warehousing_date)=QUARTER(NOW()) and DATE_FORMAT(ps.warehousing_date,'%Y') = DATE_FORMAT(NOW(),'%Y') ");
//		}
		sql.append(" select 'day' productName, if(sum(num) is null,0,sum(num)) num from ( ");
		sql.append(" select 'day' productName,sum(eg.total_volume) num from chf_ex_storage es ");
		sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id and eg.deleted=0 ");
		sql.append(" where es.deleted=0 and es.warehousing_date=CURDATE() ");
		sql.append("  group by productName ");
		sql.append("  ) a ");

		sql.append(" union all ");
		sql.append(" select 'week' productName, if(sum(num) is null,0,sum(num)) num from ( ");
		sql.append(" select 'week' productName,sum(eg.total_volume) num from chf_ex_storage es ");
		sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id and eg.deleted=0 ");
		sql.append(" where es.deleted=0 and YEARWEEK(DATE_FORMAT(es.warehousing_date,'%Y-%m-%d')) = YEARWEEK(NOW()) ");
		sql.append("  group by productName ");
		sql.append("  ) a ");

		sql.append(" union all ");
		sql.append(" select 'quarter' productName, if(sum(num) is null,0,sum(num)) num from ( ");
		sql.append(" select 'quarter' productName,sum(eg.total_volume) num from chf_ex_storage es ");
		sql.append(" inner join chf_ex_goods eg on eg.ex_storage_id=es.id and eg.deleted=0 ");
		sql.append(" where es.deleted=0 and QUARTER(es.warehousing_date)=QUARTER(NOW()) and DATE_FORMAT(es.warehousing_date,'%Y') = DATE_FORMAT(NOW(),'%Y') ");
		sql.append("  group by productName ");
		sql.append("  ) a ");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<ExStorage> listMonth(ExStorage exStorage, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ExStorage> rm = new BeanPropertyRowMapper<ExStorage>(ExStorage.class);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select DATE_FORMAT(eg.warehousing_date,'%Y-%m') productName,count(1) num from chf_ex_storage eg ");
		sql.append(" where eg.deleted=0 ");
//		sql.append(" where ps.deleted=0 and DATE_FORMAT(es.warehousing_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ");

		if (Utils.isNotEmpty(exStorage)) {
			if(Utils.isNotEmpty(exStorage.getDate1())) {
				sql.append("  and DATE_FORMAT(eg.warehousing_date,'%Y-%m')>=:date1 ");
				map.put("date1", exStorage.getDate1());
			}
			if (Utils.isNotEmpty(exStorage.getDate2())) {
				sql.append("  and DATE_FORMAT(eg.warehousing_date,'%Y-%m')<=:date2 ");
				map.put("date2", exStorage.getDate2());
			}
			if(Utils.isNotEmpty(exStorage.getKeywords())) {
				sql.append("  and DATE_FORMAT(eg.warehousing_date,'%Y-%m')=:keywords ");
				map.put("keywords", exStorage.getKeywords());
			}
		}
		sql.append("  group by productName ");
		sql.append("  order by productName ");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public ExStorage getById(ExStorage exStorage) {
		RowMapper<ExStorage> rm = new BeanPropertyRowMapper<ExStorage>(ExStorage.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select e.id,e.organization_id,e.put_storage_ids,e.specify_job_number");
    sql.append(",e.po_code,e.reference_id,e.so_code,e.customer_code");
		sql.append(",e.transfer_order_no,e.start_date,e.warehousing_date,e.arrival_date");
		sql.append(",e.vessel,e.voyage,e.carrier,e.product_name");
		sql.append(",e.box_type,e.bill_of_lading,e.port_of_departure,e.port_of_destination");
		sql.append(",e.product_type,e.pack_num,e.track_edi,e.delivery_state");
		sql.append(",e.total_gross_weight,e.volume_m3,e.cost_weight,e.weight_conversion_factor");
		sql.append(",e.billing_ton,e.bubble_separation,e.num,e.weight");
		sql.append(",e.volume,e.warehouse_name,e.billing_method,e.order_num");
		sql.append(",e.create_time,e.create_user,e.update_time,e.update_user");
    sql.append(",sogzn.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_ex_storage e ");
    sql.append(" inner join sys_user suser on suser.user_id=e.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=e.organization_id and sogzn.deleted=0 ");
    sql.append(" where e.deleted=0 and e.id=:id ");
		map.put("id", exStorage.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public Integer save(ExStorage exStorage, User loginUser) {
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		KeyHolder keyHolder=new GeneratedKeyHolder();
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into chf_ex_storage (");
    sql.append("organization_id,put_storage_ids,specify_job_number,po_code,reference_id");
		sql.append(",so_code,customer_code,transfer_order_no,start_date");
		sql.append(",warehousing_date,arrival_date,vessel,voyage");
		sql.append(",box_type,bill_of_lading,port_of_departure,port_of_destination");
		sql.append(",carrier,product_name,product_type,pack_num");
		sql.append(",track_edi,delivery_state,total_gross_weight,volume_m3");
		sql.append(",cost_weight,weight_conversion_factor,billing_ton,bubble_separation");
		sql.append(",num,weight,volume,warehouse_name");
		sql.append(",billing_method,order_num,create_user,create_time) values ( ");
    sql.append(":organization_id,:put_storage_ids,:specify_job_number,:po_code,:reference_id");
		sql.append(",:so_code,:customer_code,:transfer_order_no,:start_date");
		sql.append(",:warehousing_date,:arrival_date,:vessel,:voyage");
		sql.append(",:box_type,:bill_of_lading,:port_of_departure,:port_of_destination");
		sql.append(",:carrier,:product_name,:product_type,:pack_num");
		sql.append(",:track_edi,:delivery_state,:total_gross_weight,:volume_m3");
		sql.append(",:cost_weight,:weight_conversion_factor,:billing_ton,:bubble_separation");
		sql.append(",:num,:weight,:volume,:warehouse_name");
		sql.append(",:billing_method,:order_num,:create_user,now())");
    map.put("organization_id", loginUser.getOrganizationId() + "");
    map.put("put_storage_ids", exStorage.getPutStorageIds());
    map.put("specify_job_number", exStorage.getSpecifyJobNumber());
    map.put("po_code", exStorage.getPoCode());
    map.put("reference_id", exStorage.getReferenceId());
    map.put("so_code", exStorage.getSoCode());
    map.put("customer_code", exStorage.getCustomerCode());
    map.put("transfer_order_no", exStorage.getTransferOrderNo());
    map.put("start_date", exStorage.getStartDate());
    map.put("warehousing_date", exStorage.getWarehousingDate());
    map.put("arrival_date", exStorage.getArrivalDate());
    map.put("vessel", exStorage.getVessel());
    map.put("voyage", exStorage.getVoyage());
    map.put("carrier", exStorage.getCarrier());
    map.put("product_name", exStorage.getProductName());
    map.put("product_type", exStorage.getProductType());
    map.put("pack_num", exStorage.getPackNum());
    map.put("box_type", exStorage.getBoxType());
    map.put("bill_of_lading", exStorage.getBillOfLading());
    map.put("port_of_departure", exStorage.getPortOfDeparture());
    map.put("port_of_destination", exStorage.getPortOfDestination());
    map.put("track_edi", exStorage.getTrackEdi());
    map.put("delivery_state", exStorage.getDeliveryState());
    map.put("total_gross_weight", exStorage.getTotalGrossWeight());
    map.put("volume_m3", exStorage.getVolumeM3());
    map.put("cost_weight", exStorage.getCostWeight());
    map.put("weight_conversion_factor", exStorage.getWeightConversionFactor());
    map.put("billing_ton", exStorage.getBillingTon());
    map.put("bubble_separation", exStorage.getBubbleSeparation());
    map.put("num", exStorage.getNum()==null?null:exStorage.getNum()+"");
    map.put("weight", exStorage.getWeight()==null?null:exStorage.getWeight()+"");
    map.put("volume", exStorage.getVolume()==null?null:exStorage.getVolume()+"");
    map.put("warehouse_name", exStorage.getWarehouseName());
    map.put("billing_method", exStorage.getBillingMethod());
    map.put("order_num", exStorage.getOrderNum()==null?null:exStorage.getOrderNum()+"");
		map.put("create_user", loginUser.getUserId() + "");
		getMapParameter(sql, map);
		//		System.err.println("受到影响的行数=" + retVal + ",
		int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
			@Override
			public boolean hasValue(String paramName) {
				return map.containsKey(paramName);
			}
			@Override
			public Object getValue(String paramName) throws IllegalArgumentException {
				return map.get(paramName);
			}
		}, generatedKeyHolder);
		Integer id = generatedKeyHolder.getKey().intValue();
		return id;
	}

	@Override
	public boolean update(ExStorage exStorage, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_ex_storage set ");
    sql.append("specify_job_number=:specify_job_number,put_storage_ids=:put_storage_ids,po_code=:po_code,reference_id=:reference_id ");
		sql.append(",so_code=:so_code,customer_code=:customer_code,transfer_order_no=:transfer_order_no ");
		sql.append(",start_date=:start_date,warehousing_date=:warehousing_date,arrival_date=:arrival_date ");
		sql.append(",vessel=:vessel,voyage=:voyage,carrier=:carrier ");
		sql.append(",product_name=:product_name,product_type=:product_type,pack_num=:pack_num ");
		sql.append(",box_type=:box_type,bill_of_lading=:bill_of_lading,port_of_departure=:port_of_departure,port_of_destination=:port_of_destination");
		sql.append(",track_edi=:track_edi,delivery_state=:delivery_state,total_gross_weight=:total_gross_weight ");
		sql.append(",volume_m3=:volume_m3,cost_weight=:cost_weight,weight_conversion_factor=:weight_conversion_factor ");
		sql.append(",billing_ton=:billing_ton,bubble_separation=:bubble_separation,num=:num ");
		sql.append(",weight=:weight,volume=:volume,warehouse_name=:warehouse_name ");
		sql.append(",billing_method=:billing_method,order_num=:order_num,update_time=now() ");
		sql.append(",update_user=:update_user where id=:id ");
    map.put("put_storage_ids", exStorage.getPutStorageIds());
    map.put("specify_job_number", exStorage.getSpecifyJobNumber());
    map.put("po_code", exStorage.getPoCode());
    map.put("reference_id", exStorage.getReferenceId());
    map.put("so_code", exStorage.getSoCode());
    map.put("customer_code", exStorage.getCustomerCode());
    map.put("transfer_order_no", exStorage.getTransferOrderNo());
    map.put("start_date", exStorage.getStartDate());
    map.put("warehousing_date", exStorage.getWarehousingDate());
    map.put("arrival_date", exStorage.getArrivalDate());
    map.put("vessel", exStorage.getVessel());
    map.put("voyage", exStorage.getVoyage());
    map.put("carrier", exStorage.getCarrier());
    map.put("product_name", exStorage.getProductName());
    map.put("product_type", exStorage.getProductType());
    map.put("pack_num", exStorage.getPackNum());
		map.put("box_type", exStorage.getBoxType());
		map.put("bill_of_lading", exStorage.getBillOfLading());
		map.put("port_of_departure", exStorage.getPortOfDeparture());
		map.put("port_of_destination", exStorage.getPortOfDestination());
    map.put("track_edi", exStorage.getTrackEdi());
    map.put("delivery_state", exStorage.getDeliveryState());
    map.put("total_gross_weight", exStorage.getTotalGrossWeight());
    map.put("volume_m3", exStorage.getVolumeM3());
    map.put("cost_weight", exStorage.getCostWeight());
    map.put("weight_conversion_factor", exStorage.getWeightConversionFactor());
    map.put("billing_ton", exStorage.getBillingTon());
    map.put("bubble_separation", exStorage.getBubbleSeparation());
    map.put("num", exStorage.getNum()==null?null:exStorage.getNum()+"");
    map.put("weight", exStorage.getWeight()==null?null:exStorage.getWeight()+"");
    map.put("volume", exStorage.getVolume()==null?null:exStorage.getVolume()+"");
    map.put("warehouse_name", exStorage.getWarehouseName());
    map.put("billing_method", exStorage.getBillingMethod());
    map.put("order_num", exStorage.getOrderNum()==null?null:exStorage.getOrderNum()+"");
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", exStorage.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}


	@Override
	public boolean updateNum(ExStorage exStorage, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_ex_storage set ");
		sql.append(" num = num + :num ");
		sql.append(" ,total_gross_weight = total_gross_weight + :total_gross_weight ");
		sql.append(" ,volume_m3 = volume_m3 + :volume_m3 ");
//    sql.append(" ,total_volume=total_volume + :total_volume ");
		sql.append(",update_time=now(),update_user=:update_user where id=:id ");
		map.put("num", exStorage.getNum()+"");
		map.put("total_gross_weight", exStorage.getTotalGrossWeight());
		map.put("volume_m3", exStorage.getVolumeM3());
//    map.put("total_volume", stock.getTotalVolume());
		map.put("update_user", loginUser.getUserId()+"");
		map.put("id", exStorage.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean remove(ExStorage exStorage, User loginUser) {
		int count = 0;
		Map<String, String> map = null;
		if(exStorage.getId().length()>0){
			for(String ID : exStorage.getId().split(",")){
				map  = new HashMap<String, String>();
				if(ID==null || ID.equals("")){ break; }
				StringBuffer sql =new StringBuffer("update chf_ex_storage set deleted=1" +
						",update_user=:update_user, update_time=now() "
						+ " where id =:id ");
				map.put("update_user", loginUser.getUserId() + "");
				map.put("id", ID);
				getMapParameter(sql, map);
				count = jdbcTemplate.update(sql.toString(),map);
			}
		}
		return count > 0;
	}
}
